/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.jh.jutil.sql.client;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.TimeUnit;

/**
 *
 * @author root
 */
public class MysqlClient {

    private final String _host;
    private final int _port;
    private final String _dbname;
    private final String _user;
    private final String _password;
    private BlockingQueue<Connection> pool;
    private String url;
    public static MysqlClient _instance;

    public MysqlClient(String _host, int _port, String _dbname, String _user, String _password, int poolSize) {
        this._host = _host;
        this._port = _port;
        this._dbname = _dbname;
        this._user = _user;
        this._password = _password;
        init(poolSize);
    }

    private boolean init(int poolsize) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            url = "jdbc:mysql://" + _host + ":" + _port + "/" + _dbname + "?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&interactiveClient=true&" + "&user=" + _user
                    + "&password=" + _password;

            BlockingQueue<Connection> cnnPool = new ArrayBlockingQueue<Connection>(poolsize);
            while (cnnPool.size() < poolsize) {
                cnnPool.add(DriverManager.getConnection(url));
            }
            pool = cnnPool;

        } catch (Exception ex) {

            return false;
        }
        return true;
    }

    public Connection getDbConnection() {
        Connection conn = null;
        int retry = 0;
        do {
            try {
                conn = pool.poll(10000, TimeUnit.MILLISECONDS);
                if (conn == null || !conn.isValid(0)) {
                    conn = DriverManager.getConnection(url);
                }
            } catch (Exception ex) {

            }
            ++retry;
        } while (conn == null && retry < 3);
        return conn;
    }

    public void releaseDbConnection(java.sql.Connection conn) {
        if (conn != null) {
            pool.add(conn);
        }
    }

    public void importData(String tableName, String filename) {
        Statement stmt;
        String query;
        Connection conn = getDbConnection();
        if (conn == null) {

            return;
        }
        try {
            stmt = conn.createStatement(
                    ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            query = "LOAD DATA LOCAL INFILE '" + filename
                    + "' REPLACE INTO TABLE " + tableName;

            stmt.executeUpdate(query);

        } catch (Exception e) {
            stmt = null;
        } finally {
            releaseDbConnection(conn);
        }
    }
}
